#!/bin/sh
# SN Linux Tool v1.0 by  - sn-wow.de
############################################################################
#
#  Tool Configuration
#
#  user - MYSQL username
#  pass - MYSQL password
#  ldb - your logon database
#  cdb - your char database
#  wdb - Your world database
#  moon - Set this to 1 if you want to install moonplusplus_full.sql to be imported also, else set it to 0
#
############################################################################
user="user"
pass="pass"
port="3306"
ldb="wow_logon"
cdb="wow_char"
wdb="wow_world"

############################################################################
#
#  Server configuration, do not edit past this point
#
############################################################################
server="localhost"
devpath=development/ascent_world
uppath=development/changesets
bkpath=backup

############################################################################
#
#  Method to print tool's logo
#
############################################################################
logo() {
	clear
	echo
	echo " Silence Network Database "
	echo
	echo "http://www.sn-wow.de"
	echo 
}

############################################################################
#
#  Create a backup folder, if one doesn't exist
#
############################################################################
if [ ! -d $bkpath ]; then
	mkdir $bkpath
	chmod 0755 $bkpath
fi

############################################################################
#
#  Main program
#
############################################################################
until [ $option == x ]
do
	logo
	echo " i - Install Clean SN-DB World Database"
	echo " u - Update SN-DB World Database"
	echo " b - Backup Logon and Char Database"
	echo " r - Restore Logon and Char Database Backup"
	echo " x - Exit Tool"
	echo
	read -p " Enter option:  " option
	if [ ! $option ]; then
		echo
		read -p " Incorrect option. Press enter key to try again..."
		echo
	else
	if [ $option == i ]; then
		logo
		

		mysqldump -h $server --user=$user --port=$port --password=$pass --add-drop-table --no-data $wdb | grep ^DROP | mysql -h $server --user=$user --port=$port --password=$pass $wdb
		echo

		echo
		echo " [Cleaning World DB] Finished..."
		
		echo " [1/92] import: ai_agents.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/ai_agents.sql

		echo " [2/92] import: ai_threattospellid.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/ai_threattospellid.sql

		echo " [3/92] import: creature_formations.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/creature_formations.sql

		echo " [4/92] import: creature_names.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/creature_names.sql

		echo " [5/92] import: creature_proto.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/creature_proto.sql

		echo " [6/92] import: petdefaultspells.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/petdefaultspells.sql

		echo " [7/92] import: npc_gossip_textid.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/npc_gossip_textid.sql

		echo " [8/82] import: npc_monstersay.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/npc_monstersay.sql

		echo " [9/92] import: npc_text.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/npc_text.sql

		echo " [10/92] import: auctionhouse.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/auctionhouse.sql

		echo " [11/92] import: ItemPetFood.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/ItemPetFood.sql

		echo " [12/92] import: transport_creatures.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/transport_creatures.sql

		echo " [13/92] import: creature_timed_emotes.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/creature_timed_emotes.sql

		echo " [14/92] import: loot_creatures.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/loot_creatures.sql

		echo " [15/92] import: loot_fishing.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/loot_fishing.sql

		echo " [16/92] import: loot_items.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/loot_items.sql

		echo " [17/92] import: item_randomprop_groups.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/item_randomprop_groups.sql

		echo " [18/92] import: item_randomsuffix_groups.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/item_randomsuffix_groups.sql

		echo " [19/92] import: loot_gameobjects.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/loot_gameobjects.sql

		echo " [20/92] import: loot_prospecting.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/loot_prospecting.sql

		echo " [21/92] import: loot_disenchanting.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/loot_disenchanting.sql

		echo " [22/92] import: loot_pickpocketing.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/loot_pickpocketing.sql

		echo " [23/92] import: loot_skinning.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/loot_skinning.sql

		echo " [24/92] import: itempages.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/itempages.sql

		echo " [25/92] import: items.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/items.sql

		echo " [26/92] import: vendors.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/vendors.sql

		echo " [27/92] import: gameobject_quest_finisher.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/gameobject_quest_finisher.sql

		echo " [28/92] import: gameobject_quest_starter.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/gameobject_quest_starter.sql

		echo " [29/92] import: creature_quest_finisher.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/creature_quest_finisher.sql

		echo " [30/92] import: creature_quest_starter.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/creature_quest_starter.sql

		echo " [31/92] import: quests.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/quests.sql

		echo " [32/92] import: gameobject_quest_item_binding.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/gameobject_quest_item_binding.sql

		echo " [33/92] import: gameobject_quest_pickup_binding.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/gameobject_quest_pickup_binding.sql

		echo " [34/92] import: reputation_creature_onkill.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/reputation_creature_onkill.sql

		echo " [35/92] import: reputation_faction_onkill.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/reputation_faction_onkill.sql

		echo " [36/92] import: reputation_instance_onkill.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/reputation_instance_onkill.sql

		echo " [37/92] import: playercreateinfo.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/playercreateinfo.sql

		echo " [38/92] import: playercreateinfo_bars.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/playercreateinfo_bars.sql

		echo " [39/92] import: playercreateinfo_items.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/playercreateinfo_items.sql

		echo " [40/92] import: playercreateinfo_skills.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/playercreateinfo_skills.sql

		echo " [41/92] import: playercreateinfo_spells.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/playercreateinfo_spells.sql

		echo " [42/92] import: creature_spawns.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/creature_spawns.sql

		echo " [43/92] import: creature_staticspawns.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/creature_staticspawns.sql

		echo " [44/92] import: creature_waypoints.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/creature_waypoints.sql

		echo " [45/92] import: fishing.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/fishing.sql

		echo " [46/92] import: gameobject_spawns.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/gameobject_spawns.sql

		echo " [47/92] import: gameobject_staticspawns.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/gameobject_staticspawns.sql

		echo " [48/92] import: banned_pharses.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/banned_pharses.sql

		echo " [49/92] import: weather.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/weather.sql

		echo " [50/92] import: worldmap_info.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/worldmap_info.sql

		echo " [51/92] import: zoneguards.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/zoneguards.sql

		echo " [52/92] import: transport_data.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/transport_data.sql

		echo " [53/92] import: areatriggers.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/areatriggers.sql

		echo " [54/92] import: recall.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/recall.sql

		echo " [55/92] import: graveyards.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/graveyards.sql

		echo " [56/92] import: spelloverride.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/spelloverride.sql

		echo " [57/92] import: spell_disable.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/spell_disable.sql

		echo " [58/92] import: spell_disable_trainers.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/spell_disable_trainers.sql

		echo " [59/92] import: teleport_coords.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/teleport_coords.sql

		echo " [60/92] import: totemspells.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/totemspells.sql

		echo " [61/92] import: trainer_defs.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/trainer_defs.sql

		echo " [62/92] import: trainer_spells.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/trainer_spells.sql

		echo " [63/92] import: spell_proc_data.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/spell_proc_data.sql

		echo " [64/92] import: spell_coef_override.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/spell_coef_override.sql

		echo " [65/92] import: gameobject_names.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/gameobject_names.sql

		echo " [66/92] import: creature_names_localized.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/creature_names_localized.sql

		echo " [67/92] import: gameobject_names_localized.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/gameobject_names_localized.sql

		echo " [68/92] import: itempages_localized.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/itempages_localized.sql

		echo " [69/92] import: items_localized.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/items_localized.sql

		echo " [70/92] import: instance_bosses.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/instance_bosses.sql

		echo " [71/92] import: npc_text_localized.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/npc_text_localized.sql

		echo " [72/92] import: quests_localized.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/quests_localized.sql

		echo " [73/92] import: clientaddons.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/clientaddons.sql

		echo " [74/92] import: command_overrides.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/command_overrides.sql

		echo " [75/92] import: map_checkpoint.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/map_checkpoint.sql

		echo " [76/92] import: wordfilter_character_names.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/wordfilter_character_names.sql

		echo " [77/92] import: wordfilter_chat.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/wordfilter_chat.sql

		echo " [78/92] import: item_quest_association.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/item_quest_association.sql

		echo " [79/92] import: spell_effects_override.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/spell_effects_override.sql

		echo " [80/92] import: spell_proc.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/spell_proc.sql

		echo " [81/92] import: spellfixes.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/spellfixes.sql

		echo " [82/92] import: professiondiscoveries.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/professiondiscoveries.sql
		
		echo " [83/92] import: unit_display_sizes.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/unit_display_sizes.sql

		echo " [84/92] import: worldmap_info_localized.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/worldmap_info_localized.sql

		echo " [85/92] import: worldstring_tables.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/worldstring_tables.sql

		echo " [86/92] import: worldstring_tables_localized.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/worldstring_tables_localized.sql

		echo " [87/92] import: worldbroadcast_localized.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/worldbroadcast_localized.sql

		echo " [88/92] import: worldbroadcast.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/worldbroadcast.sql

		echo " [89/92] import: loot_milling.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/loot_milling.sql

		echo " [90/92] import: db_version.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/db_version.sql

		echo " [91/92] import: itemnames.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/itemnames.sql

		echo " [92/92] import: vendor_restrictions.sql"
		mysql -h $server --user=$user --port=$port --password=$pass $wdb < $devpath/vendor_restrictions.sql
		
		echo
		echo " [Importing] Finished..."
		fi
	fi
	if [ $option == b ]; then
		logo
		
		echo
		echo " [Deleting Old Backups] Finished..."
		rm -rf $bkpath/logon_backup.sql
		rm -rf $bkpath/character_backup.sql
		
		echo		
		echo " [Backing Up Logon Database] Finished..."
		mysqldump -h $server --user=$user --port=$port --password=$pass $ldb accounts ipbans > $bkpath/logon_backup.sql
		
		echo " [Backing Up Char Database] Finished..."
		mysqldump -h $server --user=$user --port=$port --password=$pass $cdb account_data account_forced_permissions arenateams auctions banned_names characters characters_insert_queue charters clientaddons command_overrides corpses gm_tickets groups guilds guild_bankitems guild_banklogs guild_banktabs guild_data guild_logs guild_ranks instances mailbox mailbox_insert_queue playercooldowns playeritems playeritems_insert_queue playerpets playerpetspells playersummonspells questlog server_settings social_friends social_ignores tutorials > $bkpath/character_backup.sql
		
		echo
		echo " [Backing Up] Finished..."
	fi
	if [ $option == r ]; then
		logo
		
		echo
		echo " [Emptying Logon Database] Finished..."
		mysqldump -h $server --user=$user --port=$port --password=$pass --add-drop-table --no-data $ldb | grep ^DROP | mysql -h $server --user=$user --port=$port --password=$pass $ldb
		
		echo " [Restoring Logon Database From Backup] Finished..."
		mysql -h $server --user=$user --port=$port --password=$pass $ldb < $bkpath/logon_backup.sql
		
		echo
		echo " [Emptying Char Database] Finished..."
		mysqldump -h $server --user=$user --port=$port --password=$pass --add-drop-table --no-data $cdb | grep ^DROP | mysql -h $server --user=$user --port=$port --password=$pass $cdb
		
		echo " [Restoring Char Database From Backup] Finished..."
		mysql -h $server --user=$user --port=$port --password=$pass $cdb < $bkpath/character_backup.sql
		
		echo
		echo " [Restoring Backup] Finished..."
	fi
	if [ $option == u ]; then
		logo
		
		echo
		echo " Here's a list of available updates:"
		echo
		for i in $( ls $uppath/*.sql -C -1 | cut -d'.' -f1 );
			do
				if [ $i != "changesets/" ]; then
					echo " " $i.sql
				fi
            done
			
		echo
		read -p " Which update would you like to import:  " update
		if [ ! -f "$update" ]; then
			echo
			echo " "$update " file does not exist"
		else
			echo
			mysql -h $server --user=$user --port=$port --password=$pass $wdb < $update
			echo
			echo " [Updating SN-DB World Database] Finished..."
		fi
	fi
	if [ $option != x ]; then
		echo
		read -p " Press any key to continue..."
		echo
	fi
	echo " Thanks for using SN-DB."
	done
